Step 5: SQL Queries & Use Cases (Netflix)

1. Count Active concurrent screens for an Account

Calculates current active stream sessions (heartbeats updated within the last 30 seconds) to enforce concurrent screen caps:

SELECT a.account_id, a.subscription_plan,
       COUNT(wh.history_id) AS active_screens,
       CASE 
           WHEN a.subscription_plan = 'BASIC' THEN 1
           WHEN a.subscription_plan = 'STANDARD' THEN 2
           WHEN a.subscription_plan = 'PREMIUM' THEN 4
           ELSE 0
       END AS allowed_screens
FROM accounts a
JOIN profiles p ON a.account_id = p.account_id
JOIN watch_history wh ON p.profile_id = wh.profile_id
WHERE a.account_id = 4501
  AND wh.is_completed = FALSE
  -- Streams that reported activity within the last 30 seconds
  AND wh.last_updated_time >= CURRENT_TIMESTAMP - INTERVAL '30 second'
GROUP BY a.account_id, a.subscription_plan;

2. Fetch "Continue Watching" List with TV Show Hierarchy Details

Retrieves unfinished items from a profile's history, resolving episode, season, and parent show titles when the video subtype is an Episode:

SELECT v.video_id, v.title AS video_name, v.video_type,
       wh.last_watched_position_seconds,
       ROUND((wh.last_watched_position_seconds * 100.0) / v.duration_seconds, 2) AS progress_percentage,
       -- Resolve parent show and season names if it is a TV episode
       s.title AS tv_show_name,
       se.season_number,
       ep.episode_number
FROM watch_history wh
JOIN videos v ON wh.video_id = v.video_id
LEFT JOIN episodes ep ON v.video_id = ep.episode_id
LEFT JOIN seasons se ON ep.season_id = se.season_id
LEFT JOIN shows s ON se.show_id = s.show_id
WHERE wh.profile_id = 9082
  AND wh.is_completed = FALSE
ORDER BY wh.last_updated_time DESC
LIMIT 10;

3. Fetch Kids-Safe Catalog List (Maturity Filtering)

Enforces strict age-appropriateness filters, blocking anything above PG or TV-Y7 for profiles marked as is_kids = TRUE:

SELECT v.video_id, v.title, v.maturity_rating, v.video_type,
       -- Display director for movies, or parent show title for episodes
       COALESCE(m.director, s.title) AS source_metadata
FROM videos v
LEFT JOIN movies m ON v.video_id = m.movie_id
LEFT JOIN episodes ep ON v.video_id = ep.episode_id
LEFT JOIN seasons se ON ep.season_id = se.season_id
LEFT JOIN shows s ON se.show_id = s.show_id
WHERE v.maturity_rating NOT IN ('R', 'NC-17', 'TV-MA')
  -- Kids rating safety subset
  AND v.maturity_rating IN ('G', 'PG', 'TV-Y', 'TV-Y7', 'TV-G')
ORDER BY v.title;

This content is reserved for Premium Members.